/* ------------------- Mon An---------------------*/

/* Select Mon An - Nguyen Thi Yen */
Create proc sp_SelectMonAnAll @tongmonan int out
as
	begin
		set @tongmonan = (select count(Ma_Mon)
								from MonAn)

		select ma.Ma_Mon,ma.TenMon,ma.Loai, ma.GiaTien
		from MonAn ma
	end
go

/* Select Mon An By Id - Nguyen Thi Yen */
Create proc sp_SelectMonAnById @maMon nchar(3)
as
	begin
		select ma.Ma_Mon,ma.TenMon,ma.Loai, ma.GiaTien
		from MonAn ma
		where ma.Ma_Mon =@maMon
	end
go

/* Insert Mon An - Nguyen Thi Yen */
Create proc sp_InsertMonAn
	@maMon nchar(3),
	@tenMon	nvarchar(50),
	@loai nchar(3),
	@giaTien float
as
	begin
		Insert into MonAn(Ma_Mon,TenMon,Loai, GiaTien)
		Values (@maMon ,@tenMon	,@loai,@giaTien)	
	end
go

/* Update Mon An - Nguyen Thi Yen */
Create proc sp_UpdateMonAnById
	@maMon nchar(3),
	@tenMon	nvarchar(50),
	@loai nchar(3),
	@giaTien float
as
	begin
		Update MonAn
		set TenMon=@tenMon,Loai=@loai,GiaTien=@giaTien
		where Ma_Mon=@maMon
	end
go
/* Delete Mon An - Nguyen Thi Yen */
create proc sp_DeleteMonAnById @maMon nchar(3)
as
	begin	
		delete from MonAn 
		where Ma_Mon = @maMon
	end
go

/* Create ID Mon An - Nguyen Thi Yen */
create proc sp_CreateMonAnId
as
	begin
		declare @max nchar(3)
		select @max = max(Ma_Mon) from MonAn
		if(@max is NULL)
			select '001'
		else
			begin
				declare @so int
				set @so = cast(@max as int)+1
				select cast(@so as nchar(3))		
			end
	end
go



/* ------------------- Phieu Giao---------------------*/

/* Select Phieu Giao - Nguyen Thi Yen */
create proc sp_SelectPhieuGiaoAll
as
	begin
		select pg.Ma_PG,pg.Ma_PD,pg.NV_Giao,pg.NgayGiao,pg.ThanhTien,pg.TinhTrang,pg.Ghi_Chu
		from PhieuGiao pg
	end
go

/* Select Phieu Giao By Id - Nguyen Thi Yen */
create proc sp_SelectPhieuGiaoById @maPG nchar(3)
as
	begin
		select pg.Ma_PG,pg.Ma_PD,pg.NV_Giao,pg.NgayGiao,pg.ThanhTien,pg.TinhTrang,pg.Ghi_Chu
		from PhieuGiao pg
		where pg.Ma_PG = @maPG
	end
go

/* Insert Phieu Giao - Nguyen Thi Yen */
create proc sp_InsertPhieuGiao
	@maPG nchar(3),
	@maPD nchar(3),
	@nVGiao nchar(3),
	@ngayGiao datetime,
	@thanhTien float,
	@tinhTrang int,
	@ghiChu	nvarchar(100)
as
	begin
		Insert into PhieuGiao (Ma_PG,Ma_PD,NV_Giao,NgayGiao,ThanhTien,TinhTrang,Ghi_Chu)
		Values (@maPG,@maPD,@nVGiao,@ngayGiao,@thanhTien,@tinhTrang,@ghiChu)
	end
go



/* Delete Phieu Giao - Nguyen Thi Yen */
create proc sp_DeletePhieuGiaoById @maPG nchar(3)
as
	begin
		update PhieuGiao
		set TinhTrang=0 --TinhTrang="huy"
		where Ma_PG=@maPG
	end
go

/* Create ID Phieu Giao - Nguyen Thi Yen */
create proc sp_CreatePhieuGiaoId
as
	begin
		declare @max nchar(3)
		select @max = max(Ma_PG) from PhieuGiao
		if(@max is NULL)
			select '001'
		else
			begin
				declare @so int
				set @so = cast(@max as int)+1
				select cast(@so as nchar(3))		
			end
	end
go



/* ------------- Chi Tiet Phieu Giao ---------------------*/

/* Select Chi Tiet Phieu Giao - Nguyen Thi Yen */
create proc sp_SelectChiTietPhieuGiaoAll
as
	begin
		select MaChiTietPG, Ma_PG,Ma_Mon,SoLuong,DonGia,SoHop
		from ChiTietPhieuGiao
	end
go

/* Select Chi Tiet Phieu Giao By Id - Nguyen Thi Yen */
create proc sp_SelectChiTietPhieuGiaoById @maChiTietPG nchar(3)
as
	begin
		select MaChiTietPG, Ma_PG,Ma_Mon,SoLuong,DonGia,SoHop
		from ChiTietPhieuGiao
		where MaChiTietPG = @maChiTietPG
	end
go

/* Insert Chi Tiet Phieu Giao - Nguyen Thi Yen */
create proc sp_InsertChiTietPhieuGiao
	@maChiTietPG nchar(3),
	@ma_PG nchar(3),
	@ma_Mon nchar(3),
	@soLuong int,
	@donGia float,
	@soHop int
as
	begin
		Insert into ChiTietPhieuGiao(MaChiTietPG, Ma_PG,Ma_Mon,SoLuong,DonGia,SoHop)
		Values (@maChiTietPG,@ma_PG,@ma_Mon,@soLuong,@donGia,@soHop)
	end
go



/* Delete Chi Tiet Phieu Giao - Nguyen Thi Yen */
create proc sp_DeleteChiTietPhieuGiaoById @maChiTietPG nchar(3)
as
	begin
		delete from ChiTietPhieuGiao
		where MaChiTietPG=@maChiTietPG
	end
go

/* Create ID Chi Tiet Phieu Giao - Nguyen Thi Yen */
create proc sp_CreateChiTietPhieuGiaoId
as
	begin
		declare @max nchar(3)
		select @max = max(MaChiTietPG) from ChiTietPhieuGiao
		if(@max is NULL)
			select '001'
		else
			begin
				declare @so int
				set @so = cast(@max as int)+1
				select cast(@so as nchar(3))		
			end
	end
go

/*

exec sp_SelectPhieuGiaoAll
exec sp_SelectPhieuGiaoById '001'
exec sp_InsertPhieuGiao '001','001','001','9/18/2013',1233,1,null
exec sp_DeletePhieuGiaoById '001'
exec sp_CreatePhieuGiaoId

exec sp_SelectChiTietPhieuGiaoAll
exec sp_SelectChiTietPhieuGiaoById '002'
exec sp_InsertChiTietPhieuGiao '003','001','003',12,12354,3	
exec sp_DeleteChiTietPhieuGiaoById '002'
exec sp_CreateChiTietPhieuGiaoId

exec sp_SelectMonAnAll
exec sp_SelectMonAnById '002'
exec sp_UpdateMonAnById '002',N'yến','001',30
exec sp_InsertMonAn '004',N'hehe','001',300
exec sp_CreateMonAnId
*/